package com.example.sqlitedemo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.NonNull;
import androidx.annotation.Nullable;

public class MyHelp extends SQLiteOpenHelper {
    private static final String TAG = "tag";

    //name生成的数据库文件名  factory 游标厂  version数据库版本号 通常从1 开始
    public MyHelp(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }
//数据库的初始化方法    db SQLite数据库的访问工具   通常在初始化过程中创建数据库表结构
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table stu_info(_id integer primary key autoincrement," +
                "stu_name text not null," +
                "stu_gender text not null," +
                "stu_age integer not null)");

    }
    public  void insertStuInfo(String name,String gender ,int age){
        //调用数据库时，可以通过调用getWritableDatabase()方法获取SQLiteDatabase对象
        SQLiteDatabase db=getWritableDatabase();
        //db.execSQL("insert into stu_info (stu_name,stu_gender,stu_age) values('hers','man',14)");
        db.execSQL("insert into stu_info (stu_name,stu_gender,stu_age) values('"+name+"','"+gender+"','"+age+"')");
    }
    public boolean insertStudent(@NonNull String name,@NonNull String gender,@NonNull int age){
        ContentValues cv=new ContentValues();
        cv.put("stu_name",name);
        cv.put("stu_gender",gender);
        cv.put("stu_age",age);
        long id=getWritableDatabase().insert("stu_info",null,cv);
        return id!=-1;

    }
    public boolean deleteStudent(String name){
        //参数2 where语句的占位符部分   参数3 占位符对应的数据  返回值 受影响的字段
        int count=getWritableDatabase().delete("stu_info","stu_name=?",new String[]{name});
        return count>0;
    }
    public void updateStudent(String name,int age){
        //update时的ContentValues只需要给需要更改的字段  不需要提供所有的字段值
        ContentValues cv=new ContentValues();
        cv.put("stu_age",age);
        int count=getWritableDatabase().update("stu_info",cv,"stu_name=?",new String[]{name});
    }

    /**
     *
     * @param db   SQLite数据库的访问对象
     * @param oldVersion  旧版本号
     * @param newVersion  新版本号
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
//数据库降级
    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        super.onDowngrade(db, oldVersion, newVersion);
    }
    public void queryAllStu(){
       Cursor cursor= getWritableDatabase().rawQuery("select * from stu_info",null);
       //select stu_name from stu_info where stu_age>18 order by _id ASC
        //选择SQLite的API方法主要是为了安全性考虑
       Cursor cursor1=getWritableDatabase().query("stu_info",new String[]{"_id","stu_name"},
               "stu_age>?",new String[]{"18"},null,null,"_id ASC");

       //游标置顶
       cursor.moveToFirst();
       //判断结果集是否为空
       if(cursor.getCount()>0){
           //遍历结果集取数据
          do {
               //获取字段数据
               String name=cursor.getString(cursor.getColumnIndexOrThrow("stu_name"));
               String gender=cursor.getString(cursor.getColumnIndexOrThrow("stu_gender"));
               Log.d(TAG,"学生名称"+name);
               Log.d(TAG,"学生性别"+gender);
           } while(cursor.moveToNext());
       }
       //游标关闭（避免内存泄露）
       cursor.close();

    }
    private void transaction(){

        SQLiteDatabase dp=getWritableDatabase();
        try {

        //开启事务
        dp.beginTransaction();
        //执行操作  需要在事务中完成的操作
        ContentValues cv=new ContentValues();
        cv.put("stu_name","小丽");
        cv.put("stu_gender","女");
        cv.put("stu_age",23);
        long id=getWritableDatabase().insert("stu_info",null,cv);
        ContentValues cv2=new ContentValues();
        cv2.put("stu_age",26);
        int count=getWritableDatabase().update("stu_info",cv,"stu_name=?",new String[]{"王哥"});
        //任何一步异常时均结束  事务中默认false
        if (id!=-1&&count>0){
            dp.setTransactionSuccessful();
        }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //结束事务   如果没有将事务结果通过setTransactionSuccessful()设置为true
            // 则事务结果默认为false，结束事务前会回滚操作
            dp.endTransaction();
        }
    }
}
